using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Passageways.DataAccess;

namespace Passageways.Modules.Rolodex
{
	/// <summary>
	/// Represents a named category for grouping contacts.
	/// </summary>
	[Serializable]
	public class Category
    {
		private int m_ID;
		private string m_Name;
		private string m_Description;
		private string m_LastUpdateBy;
		private DateTime m_LastUpdateDateTime;
		private bool m_Deleted;

		/// <summary>
		/// Initializes an empty instance of the <see cref="Category"/> class.
		/// </summary>
        private Category() { }

		/// <summary>
		/// Initializes an instance of the <see cref="Category"/> class to the category with the given ID.
		/// </summary>
		/// <param name="id"> The ID of the category to initialize. </param>
        public Category(int id)
        {
            string query = "SELECT * FROM ContactCategory WHERE ID = @ID";

			SqlParameter paramID = new SqlParameter("ID", SqlDbType.Int);
			paramID.Value = id;

			using(SqlDataReader reader = PWDatabase.ExecuteReader(Module.ConnectionString, CommandType.Text, query, paramID))
			{
				while(reader.Read())
				{
					ReadCategory(reader, this);
				}
			}
        }

		/// <summary>
		/// Gets the ID of the category.
		/// </summary>
		public int ID
		{
			get { return m_ID; }
		}

		/// <summary>
		/// Gets or sets the name of the category.
		/// </summary>
		public string Name
		{
			get { return m_Name; }
			set { m_Name = value; }
		}

		/// <summary>
		/// Gets or sets the desctiption of the category.
		/// </summary>
		public string Description
		{
			get { return m_Description; }
			set { m_Description = value; }
		}

		/// <summary>
		/// Gets the name of the user that last updated the category.
		/// </summary>
		public string LastUpdateBy
		{
			get { return m_LastUpdateBy; }
		}

		/// <summary>
		/// Gets the date and time that the category was last updated.
		/// </summary>
		public DateTime LastUpdateDateTime
		{
			get { return m_LastUpdateDateTime; }
		}

		/// <summary>
		/// Gets a value indicating whether or not the category is deleted.
		/// </summary>
		public bool Deleted
		{
			get { return m_Deleted; }
		}

		/// <summary>
		/// Gets a collection of all the <see cref="Category"/> objects.
		/// </summary>
        public static CategoryCollection Get()
        {
			CategoryCollection categories = new CategoryCollection();
			
			string query = "SELECT * FROM ContactCategory WHERE Deleted = 0 ORDER BY Name";

			using(SqlDataReader reader = PWDatabase.ExecuteReader(Module.ConnectionString, CommandType.Text, query))
			{
				while(reader.Read())
				{
					Category category = new Category();

					ReadCategory(reader, category);

					categories.Add(category);
				}
			}

            return categories;
        }

        /// <summary>
        /// Gets a <see cref="Category"/> based on a category name.
        /// </summary>
        /// <param name="name"> The name of the category to retrieve. </param>
		/// <returns> A <see cref="Category"/> if one is found, or a null reference otherwise. </returns>
        public static Category Get(string name)
        {
            string query = string.Format("SELECT * FROM ContactCategory WHERE (Name = @Name) AND (Deleted = 0)");

			SqlParameter paramName = new SqlParameter("Name", SqlDbType.VarChar, 250);
			paramName.Value = name;

            using(SqlDataReader reader = PWDatabase.ExecuteReader(Module.ConnectionString, CommandType.Text, query, paramName))
            {
                if(reader.Read())
                {
                    Category category = new Category();

                    ReadCategory(reader, category);

                    return category;
                }
            }

            return null;
        }

		/// <summary>
		/// Adds a new category.
		/// </summary>
		/// <param name="name"> The name of the new category. </param>
		/// <param name="description"> The desctiption of the new category. This value can be null. </param>
		/// <returns> The <see cref="Category"/> object that was just created. </returns>
		public static Category Add(string name, string description)
        {
			string lastUpdatedBy = PWSession.UserFullName;
			DateTime lastUpdatedOn = DateTime.Now;

			StringBuilder query = new StringBuilder();
			query.Append("INSERT INTO ContactCategory ");
			query.Append("            (Name, Description, LastUpdateBy, LastUpdateDateTime) ");
			query.Append("VALUES      (@Name, @Description, @LastUpdatedBy, @LastUpdateDateTime);");
			query.Append("SET @ID = @@IDENTITY;");

			SqlParameter paramName = new SqlParameter("Name", SqlDbType.VarChar, 250);
			paramName.Value = name;

			SqlParameter paramDescription = new SqlParameter("Description", SqlDbType.VarChar, 1500);

			if(string.IsNullOrEmpty(description))
			{
				paramDescription.Value = DBNull.Value;
			}
			else
			{
				paramDescription.Value = description;
			}

			SqlParameter paramLastUpdatedBy = new SqlParameter("LastUpdatedBy", SqlDbType.VarChar, 150);
			paramLastUpdatedBy.Value = lastUpdatedBy;

			SqlParameter paramLastUpdateDateTime = new SqlParameter("LastUpdateDateTime", SqlDbType.DateTime);
			paramLastUpdateDateTime.Value = lastUpdatedOn;

			SqlParameter paramID = new SqlParameter("ID", SqlDbType.Int);
			paramID.Direction = ParameterDirection.Output;

			PWDatabase.ExecuteNonQuery(Module.ConnectionString, CommandType.Text, query.ToString(), paramName, paramDescription, paramLastUpdatedBy, paramLastUpdateDateTime, paramID);

			Category category = new Category();
			category.m_ID = (int)paramID.Value;
			category.m_Name = name;
			category.m_Description = description;
			category.m_LastUpdateBy = lastUpdatedBy;
			category.m_LastUpdateDateTime = lastUpdatedOn;

			return category;
        }

		/// <summary>
		/// Removes a category.
		/// </summary>
		/// <param name="id"> The ID of the category to remove. </param>
        public static void Remove(string id)
        {
            string query = "UPDATE ContactCategory SET Deleted = 1 WHERE ID = @ID";

			SqlParameter paramID = new SqlParameter("ID", SqlDbType.Int);
			paramID.Value = id;

            PWDatabase.ExecuteNonQuery(Module.ConnectionString, CommandType.Text, query, paramID);
        }

		/// <summary>
		/// Commits any changed to the category.
		/// </summary>
		public void Update()
		{
			SqlParameter paramName = new SqlParameter("Name", SqlDbType.VarChar, 250);
			paramName.Value = m_Name;

			SqlParameter paramDescription = new SqlParameter("Description", SqlDbType.VarChar, 1500);

			if(string.IsNullOrEmpty(m_Description))
			{
				paramDescription.Value = DBNull.Value;
			}
			else
			{
				paramDescription.Value = m_Description;
			}

			m_LastUpdateBy = PWSession.UserFullName;

			SqlParameter paramLastUpdatedBy = new SqlParameter("LastUpdatedBy", SqlDbType.VarChar, 150);
			paramLastUpdatedBy.Value = m_LastUpdateBy;

			m_LastUpdateDateTime = DateTime.Now;

			SqlParameter paramLastUpdateDateTime = new SqlParameter("LastUpdateDateTime", SqlDbType.DateTime);
			paramLastUpdateDateTime.Value = m_LastUpdateDateTime;

			SqlParameter paramID = new SqlParameter("ID", SqlDbType.Int);
			paramID.Value = m_ID;

			StringBuilder query = new StringBuilder();
			query.Append("UPDATE ContactCategory ");
			query.Append("SET    Name = @Name, ");
			query.Append("       Description = @Description, ");
			query.Append("       LastUpdateBy = @LastUpdatedBy, ");
			query.Append("       LastUpdateDateTime = @LastUpdateDateTime ");
			query.Append("WHERE  (ID = @ID)");

			PWDatabase.ExecuteNonQuery(Module.ConnectionString, CommandType.Text, query.ToString(), paramName, paramDescription, paramLastUpdatedBy, paramLastUpdateDateTime, paramID);
		}

		/// <summary>
		/// Reads the data from a <see cref="SqlDataReader"/> into a <see cref="Category"/> object.
		/// </summary>
		/// <param name="reader"> A <see cref="SqlDataReader"/> containing category data. </param>
		/// <param name="category"> An instance of the <see cref="Category"/> class to read the data from <paramref name="reader"/> to. </param>
		private static void ReadCategory(SqlDataReader reader, Category category)
		{
			category.m_ID = (int)reader["ID"];
			category.m_Name = (string)reader["Name"];

			object description = reader["Description"];

			if(description == DBNull.Value)
			{
				category.m_Description = string.Empty;
			}
			else
			{
				category.m_Description = (string)description;
			}
			
			category.m_LastUpdateBy = (string)reader["LastUpdateBy"];
			category.m_LastUpdateDateTime = (DateTime)reader["LastUpdateDateTime"];
			category.m_Deleted = (bool)reader["Deleted"];
		}

        /// <summary>
        /// Determines whether or not a category exists based on a category name.
        /// </summary>
        /// <param name="name"> The name of the category to check for. </param>
        /// <returns> A value indicating whether or not a category exists with the name, <paramref name="name"/>. </returns>
        public static bool Exists(string name)
        {
            string query = string.Format("SELECT @Count = COUNT(*) FROM ContactCategory WHERE (Name = @Name) AND (Deleted = 0)");

			SqlParameter paramName = new SqlParameter("Name", SqlDbType.VarChar, 250);
			paramName.Value = name;

			SqlParameter paramCount = new SqlParameter("Count", SqlDbType.Int);
			paramCount.Direction = ParameterDirection.Output;

			PWDatabase.ExecuteNonQuery(Module.ConnectionString, CommandType.Text, query, paramCount, paramName);

            return (int)paramCount.Value > 0;
        }
    }
}